1 Imports System.Data.SqlClient
2 Public Class frmDiscount_Staff
3 Public Sub Getdata()
4 Try
5 con = New SqlConnection(cs)
6 con.Open()
7 cmd = New SqlCommand("SELECT RTRIM(St_ID),RTRIM(StaffID),RTRIM(StaffName) from Staff order by StaffName", con)
8 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
9 dgw.Rows.Clear()
10 While (rdr.Read() = True)
11 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
12 End While
13 con.Close()
14 Catch ex As Exception
15 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
16 End Try
17 End Sub
18 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
19 Me.Close()
20 End Sub
21 Sub Reset()
22 Getdata()
23 btnUpdate.Enabled = False
24 End Sub
25 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
26 Reset()
27 End Sub
28
29
30 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
31 Try
32 If MessageBox.Show("Do you really want to update the records?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then
33 update_records()
34 End If
35 Catch ex As Exception
36 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
37 End Try
38 End Sub
39 Sub update_records()
40 Try
41
42 If dgw.Rows.Count = 0 Then
43 MessageBox.Show("Sorry nothing to update.." & vbCrLf & "Please retrieve data in datagridview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
44 Exit Sub
45 End If
46 con = New SqlConnection(cs)
47 con.Open()
48 Dim cb As String = "delete from discount_Staff where StaffID=@d1"
49 cmd = New SqlCommand(cb)
50 cmd.Connection = con
51
52 ' Add Parameters to Command Parameters collection
53 cmd.Parameters.Add(New SqlParameter("@d1", System.Data.SqlDbType.Int, 10, "StaffID"))
54
55
56 ' Prepare command for repeated execution
57 cmd.Prepare()
58
59 ' Data to be inserted
60 For Each row As DataGridViewRow In dgw.Rows
61 If Not row.IsNewRow Then
62
63 cmd.Parameters("@d1").Value = row.Cells(0).Value.ToString
64 cmd.ExecuteNonQuery()
65 End If
66 Next
67 con.Close()
68 con = New SqlConnection(cs)
69 con.Open()
70 Dim cb1 As String = "insert into Discount_Staff(StaffID,Discount) VALUES (@d1,@d2)"
71 cmd = New SqlCommand(cb1)
72 cmd.Connection = con
73
74
75
76 ' Add Parameters to Command Parameters collection
77 cmd.Parameters.Add(New SqlParameter("@d1", System.Data.SqlDbType.Int, 10, "StaffID"))
78
79 cmd.Parameters.Add(New SqlParameter("@d2", System.Data.SqlDbType.Float, 10, "Discount"))
80
81
82 ' Prepare command for repeated execution
83 cmd.Prepare()
84
85 ' Data to be inserted
86 For Each row As DataGridViewRow In dgw.Rows
87 If Not row.IsNewRow Then
88
89 cmd.Parameters("@d1").Value = row.Cells(0).Value.ToString
90 cmd.Parameters("@d2").Value = CDbl(row.Cells(3).Value.ToString)
91 cmd.ExecuteNonQuery()
92 End If
93 Next
94 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
95 btnUpdate.Enabled = False
96 con.Close()
97 Catch ex As Exception
98 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
99 End Try
100 End Sub
101
102 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
103 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
104 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
105 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
106 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
107 End If
108 Dim b As Brush = SystemBrushes.ControlText
109 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
110
111 End Sub
112
113 Private Sub frmFeeName_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
114 Getdata()
115 End Sub
116
117 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
118 Try
119 btnUpdate.Enabled = True
120 con = New SqlConnection(cs)
121 con.Open()
122 cmd = New SqlCommand("SELECT RTRIM(Staff.St_ID),RTRIM(Staff.StaffID),RTRIM(StaffName),RTRIM(Discount) from Staff,Discount_Staff where Staff.St_ID=Discount_Staff.StaffID order by StaffName", con)
123 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
124 dgw.Rows.Clear()
125 While (rdr.Read() = True)
126 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
127 End While
128 con.Close()
129 Catch ex As Exception
130 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
131 End Try
132 End Sub
133 End Class
